package com.networkmmc.demo.services;

import cn.hutool.core.text.UnicodeUtil;
import cn.hutool.core.util.StrUtil;
import com.networkmmc.demo.data.ConvertToSql;
import com.networkmmc.demo.file.Excel;
import com.networkmmc.demo.sql.SqlObj;
import com.networkmmc.demo.sql.SqlUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Component
public class ProcessDataService {

    private Map<String, Map<String, Object>> tempMap = new HashMap<>();
    private String actualName = "";

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public void proc(String filePath, int sheetIndex, int sheetLength, boolean multiFile, int maxFileNum){
        actualName = StrUtil.subBefore(StrUtil.subAfter(filePath, "/", true), ".", true);
        if (multiFile){
            for (int i = 1; i <= maxFileNum; i++) {
                run(StrUtil.subBefore(filePath, ".", true) + i + "." + StrUtil.subAfter(filePath, ".", true), sheetIndex, sheetLength);
            }
        }else{
            run(filePath, sheetIndex, sheetLength);
        }
        System.out.println("数据导入任务已完成");
    }

    private void run(String filePath, int sheetIndex, int sheetLength){
        System.out.println("开始处理文件" + filePath);
        List<SqlObj> sqlInfo = new ArrayList<>();
        if (sheetLength == 1){
            List<List<Object>> lists = Excel.readExcel(filePath, sheetIndex);
            String tableName = generateDbTable(lists, sheetIndex);
            lists.remove(0);
            sqlInfo.addAll(ConvertToSql.toSql(lists, tableName, (String[])tempMap.get(tableName).get("fields"), (int[])tempMap.get(tableName).get("maxNums")));
        }else{
            for (int i = 0; i < sheetLength; i++) {
                List<List<Object>> lists = Excel.readExcel(filePath, sheetIndex++);
                String tableName = generateDbTable(lists, sheetIndex);
                lists.remove(0);
                sqlInfo.addAll(ConvertToSql.toSql(lists, tableName, (String[])tempMap.get(tableName).get("fields"), (int[])tempMap.get(tableName).get("maxNums")));
            }
        }
        inDb(sqlInfo);
        System.out.println("文件： " + filePath + ", 数据导入已完成");
    }

    private String generateDbTable(List<List<Object>> datas, int sheetIndex){
        char[] fileName = actualName.toCharArray();

        String tableName = "";
        for (int i = 0; i < fileName.length; i++) {
            if (StrUtil.equals(StrUtil.toString(fileName[i]), UnicodeUtil.toUnicode(StrUtil.toString(fileName[i])))){
                tableName += StrUtil.toString(fileName[i]);
            }else{
                tableName += UnicodeUtil.toUnicode(StrUtil.toString(fileName[i])).substring(5,6);
            }
        }
        tableName.replaceAll("/", "");
        tableName += sheetIndex;
        System.out.println("数据表： " + tableName + " , 描述： " + actualName + sheetIndex);
        boolean isCreate = true;
        String[] fields = new String[datas.get(0).size()];
        String[] fieldNames = new String[datas.get(0).size()];
        int[] maxNums = new int[datas.get(0).size()];
        if(tempMap.containsKey(tableName)){
            isCreate = false;
            fields = (String[])tempMap.get(tableName).get("fields");
            fieldNames = (String[])tempMap.get(tableName).get("fieldNames");
            maxNums = (int[])tempMap.get(tableName).get("maxNums");
        }else{
            for (int i = 0; i < datas.get(0).size(); i++) {
                char[] colName = StrUtil.toString(datas.get(0).get(i)).toCharArray();
                String field = "";
                for (int j = 0; j < colName.length; j++) {
                    if (StrUtil.equals(StrUtil.toString(colName[j]), UnicodeUtil.toUnicode(StrUtil.toString(colName[j])))){
                        field += StrUtil.toString(colName[j]);
                    }else{
                        field += UnicodeUtil.toUnicode(StrUtil.toString(colName[j])).substring(5,6);
                    }
                }
                field = field.replaceAll("/", "");
                System.out.println("字段名： " + field + " , 描述： " + StrUtil.toString(datas.get(0).get(i)));
                fields[i] = field;
                fieldNames[i] = StrUtil.toString(datas.get(0).get(i));
                maxNums[i] = 0;
            }
            Map<String, Object> m = new HashMap<>();
            m.put("fields", fields);
            m.put("maxNums", maxNums);
            m.put("fieldNames", fieldNames);
            tempMap.put(tableName, m);
        }
        List<String> modifySql = ConvertToSql.modifyColSql(datas, tableName, fields, maxNums, fieldNames);
        if (isCreate){
            StringBuffer sb = new StringBuffer();
            sb.append("CREATE TABLE ").append(tableName).append(" ( ");
            for (int i = 0; i < fields.length; i++) {
                sb.append("`").append(fields[i]).append("` VARCHAR(").append(maxNums[i]).append(") COMMENT '").append(fieldNames[i]).append("',");
            }
            sb.append("UPDATE_TIME_ TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间') COMMENT = '").append(actualName).append("' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");
            jdbcTemplate.update(sb.toString());
        }else{
            for (String sql: modifySql) {
                jdbcTemplate.update(sql);
            }
        }

        return tableName;
    }

    private void inDb(List<SqlObj> udid_info){
        Map<String, List<Object[]>> map = new HashMap<>();
        for (SqlObj sqlObj: udid_info) {
            if(map.containsKey(SqlUtils.insert(sqlObj))){
                map.get(SqlUtils.insert(sqlObj)).add(sqlObj.getValues().toArray());
            }else{
                List<Object[]> l = new ArrayList<>();
                l.add(sqlObj.getValues().toArray());
                map.put(SqlUtils.insert(sqlObj), l);
            }
        }
        for (Map.Entry<String, List<Object[]>> e : map.entrySet()) {
            jdbcTemplate.batchUpdate(e.getKey(), e.getValue());
        }
    }
}
